const { customer } = require('../db/model/index')
const Sequelize = require('sequelize');
const ejsExcel = require("ejsexcel");
const path = require('path');
const fs = require('fs');
const { query } = require('../config/mysqlDb.js');
const { getDateYYYYMMDDHHMMSS, findGenerationNodes, getDateDDHHMMSS,convertToTree, getDateIntSecs, getRandomInt, aliyunCiloud } = require('../utils/utils.js');
const koa2Req = require('koa2-request');
const { WX_CONFIG } = require('../config/db')//微信的配置

const api = {
    getUnionid: 'https://api.weixin.qq.com/sns/jscode2session?'
}


//网路请求
async function requesturl(url) {
    try {
        const response = await koa2Req(url)
        console.log('网络请求结果');
        console.log(response.body);
        return response.body
    } catch (error) {
        console.error(error)
    }
}

//获取微信小程序个人信息
async function getWebAccessToken(code, area) {
    const url = api.getUnionid + '&appid=' + WX_CONFIG.wxchatAppId + '&secret=' + WX_CONFIG.wxchatSecret + '&js_code=' + code + '&grant_type=authorization_code';
    const data = await requesturl(url)
    console.log('data===', JSON.parse(data).openid);

    let result = '';
    if (JSON.parse(data).openid){
        result = await getUserOpenidServer({ "openid": JSON.parse(data).openid ,"area": area })
        return { "code": 200, "data": result };
    }else{
        return { "code": 300, "data": "code无效" };
    }
}

//验证客户代码的正确性
async function getCustomer(customer, area) {
    if(area == 'shanghai'){
        const result = await query('select * from customers where customer = ?', [customer]);
        if (!result[0]) return { "code": 200, "data": null };
        return { "code": 200, "data": result[0] };
    }else{
        return { "code": 200, "data": 1 };
    }
   
}

//用户唯一性判断
async function getUserOpenidServer(data) {
    const { openid, area, createdAt = getDateYYYYMMDDHHMMSS(), updatedAt = getDateYYYYMMDDHHMMSS() } = data;
    const args = [openid, area, createdAt, updatedAt];
    const result = await query('select * from users where openid = ?', [openid]);
    let userInfo = '';
    if (result[0]) {
        userInfo = result[0];
        console.log('------老用户------');
        return userInfo;
    } else {
        const rs = await query('INSERT INTO users (openid,area,createdAt,updatedAt) VALUES (?, ?,?, ?)', args, true);
        const { insertId: id } = rs;
        console.log('------新用户------');
        return { "openid": openid };
    }
}
//发送手机短信
async function sendCodeAction(data) {
    let tel = data.tel;
    console.log('tel===', tel);
    let code = getRandomInt(6);
    console.log(code);

    //引入阿里云短信云平台
    aliyunCiloud(tel,code);

    //插入一条数据
    const args = [code, tel, getDateYYYYMMDDHHMMSS(), getDateYYYYMMDDHHMMSS()];
    console.log('args==', args);
    const result = await query('INSERT INTO codes (code, mobile,createdAt,updatedAt) VALUES (?, ?, ?, ?)', args, true);
    const { insertId: id } = result;
    return { "code": 200, "data": id };
}
//提交问答
async function addQuestionAction(ctx) {  
    const { openid, mobile, resutl1, resutl2, resutl3, resutl4, time1, time2, time3, time4, right1, right2, right3,right4,area, customerCode } = ctx.request.body
    const args = [openid, mobile, resutl1, resutl2, resutl3, resutl4, time1, time2, time3, time4, right1, right2, right3, right4, area, customerCode, getDateYYYYMMDDHHMMSS(), getDateYYYYMMDDHHMMSS()];
    const result = await query('INSERT INTO questions (openid, mobile, resutl1, resutl2, resutl3,resutl4,time1,time2,time3,time4, right1, right2, right3,right4,area,customerCode,createdAt,updatedAt) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? ,? ,? ,? , ?, ?, ?, ?)', args, true);
    const { insertId: id } = result;
    return { "code": 200, "data": id };
}
//验证码是否过期
async function wechatTelAction(ctx) {  
    const { tel, openid, code, area, createdAt = getDateYYYYMMDDHHMMSS(), updatedAt = getDateYYYYMMDDHHMMSS() } = ctx.request.body;
    //第一步检测短信验证码是否过期
    console.log(tel + '=======' + openid + '=======' + code + '===========' + area);

    if (area == 'shanghai') {//------------------------上海地区
        const list = await query(`SELECT id,code,unix_timestamp(createdAt) AS createdAt FROM codes WHERE mobile=${tel} ORDER BY createdAt DESC`);
            
        if (list.length > 0) {
                const data = list[0]
                if (Date.now() / 1000 - data.createdAt > 10 * 60) {  /// 10分钟内有效
                    console.log("验证码已过期，请重新获取")
                    return { "code": 300, "data": '验证码已过期，请重新获取' };
                } else {
                    if (data.code == code) {
                        const lt = await query('select * from users where openid = ?', [openid]);
                        console.log(lt);
                        if (lt.length > 0) {
                            //执行修改
                            const ag = [tel, getDateYYYYMMDDHHMMSS(), openid];
                            console.log(ag);
                            let rst = await query('UPDATE users SET mobile=?, updatedAt=? WHERE openid=?', ag);
                            console.log('s==', rst);
                            const { affectedRows } = rst;
                            return { "code": 200, "data": affectedRows };
                        } else {
                            return { "code": 300, "data": 'openid有误' }
                        }
                    } else {
                        console.log("验证码错误")
                        return { "code": 300, "data": '验证码错误' };
                    }
                }
        } else {
            console.log("未获取到验证码")
            return { "code": 300, "data": '未获取到验证码' };
        }
    }else{
        return { "code": 200, "data": '非上海地区' };
    }

}
//是否已参与活动判断
async function isFlagAction(data) {  
    let openid = data.openid;
    let customerCode = data.customerCode;//客户代码  以客户代码判断是否参与活动
    const result = await query('select * from questions where customerCode = ?', [customerCode]);
    console.log('1====', result);
    if (result.length > 0) { //有参与过
        return { "code": 300, "data": 1 };;
    } else {
        return { "code": 200, "data": 0 };;
    }
}
async function isOkAction(ctx) {
    const { openid, age, manage, area, customerCode, unit1, unit2, unit3, createdAt = getDateYYYYMMDDHHMMSS(), updatedAt = getDateYYYYMMDDHHMMSS() } = ctx.request.body;
    const args = [openid, age, manage, area, customerCode, unit1, unit2, unit3, createdAt, updatedAt];
    const result = await query('select * from records where customerCode = ?', [customerCode]);
    if (result[0]) {//有---更新
        const ag = [age, manage, area, unit1, unit2, unit3, updatedAt, customerCode];
        let rst = await query('UPDATE records SET age = ?, manage = ? ,area = ?, unit1 = ?, unit2 = ?, unit3 = ?,updatedAt = ? WHERE customerCode = ?', ag);
        return { "code": 200, "data": 1 };
    } else { //没有---新增
        const rs = await query('INSERT INTO records (openid, age, manage, area, customerCode, unit1, unit2, unit3, createdAt, updatedAt) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', args, true);
        return { "code": 200, "data": 1 };
    }
}
//web端数据展示
async function reactDataAction(data) {  
    let data1 = '', data2 = '', data3 = '', data4 = '', data5 = '', data6 = '', data7 = '', data8 = '', data9 = '', data10 = '';
    
    //这个不需要了
    // 1.每个城市扫二维码总人数---直接统计user表中的area数量，如area = shanghai
    // const rs1 = await query('select count(*) as total from users where area= ?', [data]);
    // data1 = rs1[0].total;

    // 2.每个城市参与游戏总人数---直接统计question表中area=shanghai的总数，customercode的数量。
    const rs2 = await query('select count(*) as total from questions where area = ? ', [data]);
    data2 = rs2[0].total;

    //这个不需要了
    // 3.每个城市未参与游戏总数人数---问题1 - 问题2
    // const rs3 = data1 - data2;
    // data3 = rs3;

    // 4.每个城市点击观看3个新品知识点的门店总数---直接查询record表中unit1,unit2,unit3都=1的总和
    const rs4 = await query('select count(*) as total from records where unit1 = 1 and unit2 = 1 and unit3 = 1 and area = ?', [data]);
    data4 = rs4[0].total;
    
    //5.每个城市4道题全部答对的门店总数-----查询question表中right1, right2, right3 ,right4 都为1的总数
    const rs5 = await query('select count(*) as total from questions where right1 = 1 and right2 = 1 and right3 = 1 and right4 = 1 and area = ?', [data]);
    data5 = rs5[0].total;

    //6.每个城市4道题全部答错的门店总数----查询question表中right1, right2, right3 ,right4 任意为0的总数
    const rs6 = await query('select count(*) as total from questions where area = ? and (right1 = 0 or right2 = 0 or right3 = 0 or right4 = 0)', [data]);
    data6 = rs6[0].total;

    //7.问题1的正确率----查询question表中right1=1 / 总数 = %
    const rs7 = await query('select count(*) as total from questions where right1 = 1 and area = ?', [data]);
    data7 = data2 > 0 ? Math.round(rs7[0].total / data2 * 10000) / 100  : 0;

    const rs8 = await query('select count(*) as total from questions where right2 = 1 and area = ?', [data]);
    data8 = data2 > 0 ? Math.round(rs8[0].total / data2 * 10000) / 100 : 0;

    const rs9 = await query('select count(*) as total from questions where right3 = 1 and area = ?', [data]);
    data9 = data2 > 0 ? Math.round(rs9[0].total / data2 * 10000) / 100 : 0;

    const rs10 = await query('select count(*) as total from questions where right4 = 1 and area = ?', [data]);
    data10 = data2 > 0 ? Math.round(rs10[0].total / data2 * 10000) / 100 : 0;

    return { "code": 200, "data": { "data1": data1, "data2": data2, "data3": data3, "data4": data4, "data5": data5, "data6": data6, "data7": data7 + '%', "data8": data8 + '%', "data9": data9 + '%', "data10": data10 + '%' } };
}

//SELECT a.area,c.customerCode, a.mobile,b.age,b.manage,b.unit1,b.unit2,b.unit3,c.resutl1,c.resutl2,c.resutl3,c.resutl4,c.time1 FROM users AS a LEFT JOIN records AS b ON a.openid = b.openid LEFT JOIN questions AS c ON a.openid = c.openid WHERE a.area = 'shanghai';
async function reactListAction(area, customer) {
    let sqlStr = ` `
    if (customer) {
        sqlStr += ` AND a.customerCode = '${customer}'`;
    }
    const list = await query(`SELECT a.id, a.area, a.customerCode, a.mobile, a.resutl1, a.resutl2, a.resutl3, a.resutl4, a.time1, b.age, b.manage, b.unit1, b.unit2, b.unit3 FROM questions AS a LEFT JOIN records AS b ON a.customerCode = b.customerCode WHERE a.area = ? ${sqlStr}`, [area]);
    return { "code": 200, "data": list}
}

async function reactdownAction(area, customer) {  
    let resObj = { "msg": "", "result": false, "error_code": "0", downloadUrl: "" };

    async function readydata() {
        let sqlStr = ` `
        if (customer) {
            sqlStr += ` AND a.customerCode = '${customer}'`;
        }
        const list = await query(`SELECT a.id, a.area, a.customerCode, a.mobile, a.resutl1, a.resutl2, a.resutl3, a.resutl4, a.time1, b.age, b.manage, b.unit1, b.unit2, b.unit3 FROM questions AS a LEFT JOIN records AS b ON a.customerCode = b.customerCode WHERE a.area = ? ${sqlStr}`, [area]);
        return list;
    }
    //导出
    async function exportdata(v) {
        console.log('v===', v);
        let titleArr = [
            { 'title': "城市" },
            { 'title': '客户代码' },
            { 'title': '手机号码' },
            { 'title': '身份验证1' },
            { 'title': '身份验证2' },
            { 'title': '知识点1' },
            { 'title': '知识点2' },
            { 'title': '知识点3' },
            { 'title': '问题1' },
            { 'title': '问题2' },
            { 'title': '问题3' },
            { 'title': '问题4' },
            { 'title': '答题时间' },
        ];
        let exlBuf = fs.readFileSync("./excel/记录表.xlsx");
        //数据源
        let data_arr = [];

        for (let i = 0; i < v.length; i++) {
            let security = [];
            security[0] = v[i].area;
            security[1] = v[i].customerCode;
            security[2] = v[i].mobile;
            security[3] = v[i].age;
            security[4] = v[i].manage;
            security[5] = v[i].unit1;
            security[6] = v[i].unit2;
            security[7] = v[i].unit3;
            security[8] = v[i].resutl1;
            security[9] = v[i].resutl2;
            security[10] = v[i].resutl3;
            security[11] = v[i].resutl4;
            security[12] = v[i].time1;
            data_arr[i] = security;
        }
        let time = getDateDDHHMMSS();
        let data = [[{ "table_name": "记录表", "date": time, "allNum": data_arr.length }],
            titleArr,
            data_arr
        ];
        let exlBuf2 = await ejsExcel.renderExcel(exlBuf, data);
        await fs.writeFileSync('./public/outputexcel/记录表_' + time + '.xlsx', exlBuf2);
        resObj.downloadUrl = '/outputexcel/记录表_' + time + '.xlsx';
        resObj.result = true;
        resObj.msg = '下载成功';
        return resObj;
    }
    let r = await readydata();
    let rs = await exportdata(r);
    return rs;
}

async function reactcityAction(pwd) {  
    const result = await query(`select * from cities where pwd like '%${pwd}%' `, [pwd]);
    if (result[0]) {
        return { "code": 200, "data": result[0] };
    } else {
        return { "code": 300, "data": '没有此城市' };
    }

}

module.exports = {
    getWebAccessToken,
    getCustomer,
    sendCodeAction,
    addQuestionAction,
    wechatTelAction,
    isFlagAction,
    reactDataAction,
    isOkAction,
    reactListAction,
    reactdownAction,
    reactcityAction
}